from flask import Flask, render_template, request, jsonify, redirect, url_for, flash, session
import mysql.connector
from datetime import datetime
import csv
from io import StringIO

app = Flask(__name__)
app.secret_key = 'your_secret_key'  # 用于会话加密

# 数据库配置
db_config = {
    'user': 'root',
    'password': '123456',
    'host': 'localhost',
    'database': 'bank_system1'
}


# 辅助函数：获取数据库连接
def get_db_connection():
    return mysql.connector.connect(**db_config)


# 首页
@app.route('/')
def index():
    return render_template('index.html')


# 登录
@app.route('/login', methods=['POST'])
def login():
    username = request.json.get('username')
    password = request.json.get('password')

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
    user = cursor.fetchone()
    cursor.close()
    conn.close()

    if user:
        session['user_id'] = user[0]
        session['username'] = user[1]

        # 检查用户是否是管理员（这里简单地假设用户名为 'admin' 的用户是管理员）
        if user[1] == 'admin':
            return jsonify({'success': True, 'message': '登录成功', 'redirect_url': url_for('admin')})
        else:
            return jsonify({'success': True, 'message': '登录成功', 'redirect_url': url_for('customer')})
    else:
        return jsonify({'success': False, 'message': '用户名或密码错误'})


# 注册
@app.route('/register', methods=['POST'])
def register():
    username = request.json.get('username')
    password = request.json.get('password')

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '注册成功', 'redirect_url': url_for('index')})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'注册失败: {str(err)}'})


# 管理员页面
@app.route('/admin')
def admin():
    if 'user_id' not in session or session['username'] != 'admin':
        return redirect(url_for('index'))
    return render_template('admin.html')


# 客户管理页面
@app.route('/customers')
def customers():
    if 'user_id' not in session or session['username'] != 'admin':
        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM customers")
    customers = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('customers.html', customers=customers)


# 添加客户
@app.route('/add_customer', methods=['POST'])
def add_customer():
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    customer_name = request.json.get('customer_name')
    id_number = request.json.get('id_number')
    telephone = request.json.get('telephone')
    address = request.json.get('address')

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(
            "INSERT INTO customers (customer_name, id_number, telephone, address) VALUES (%s, %s, %s, %s)",
            (customer_name, id_number, telephone, address)
        )
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '客户添加成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'添加客户失败: {str(err)}'})


# 删除客户
@app.route('/delete_customer/<int:customer_id>', methods=['POST'])
def delete_customer(customer_id):
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM customers WHERE customer_id = %s", (customer_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '客户删除成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'删除客户失败: {str(err)}'})


# 银行卡管理页面
@app.route('/cards')
def cards():
    if 'user_id' not in session or session['username'] != 'admin':
        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM cards")
    cards = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('cards.html', cards=cards)


# 添加银行卡
@app.route('/add_card', methods=['POST'])
def add_card():
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    card_id = request.json.get('card_id')
    open_date = request.json.get('open_date')
    open_amount = request.json.get('open_amount')
    balance = request.json.get('balance')
    password = request.json.get('password')
    is_report_loss = request.json.get('is_report_loss')
    customer_id = request.json.get('customer_id')

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(
            "INSERT INTO cards (card_id, open_date, open_amount, balance, password, is_report_loss, customer_id) VALUES (%s, %s, %s, %s, %s, %s, %s)",
            (card_id, open_date, open_amount, balance, password, is_report_loss, customer_id)
        )
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '银行卡添加成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'添加银行卡失败: {str(err)}'})


# 删除银行卡
@app.route('/delete_card/<string:card_id>', methods=['POST'])
def delete_card(card_id):
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM cards WHERE card_id = %s", (card_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '银行卡删除成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'删除银行卡失败: {str(err)}'})


# 存款业务管理页面
@app.route('/deposits')
def deposits():
    if 'user_id' not in session or session['username'] != 'admin':
        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM deposits")
    deposits = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('deposits.html', deposits=deposits)


# 添加存款业务
@app.route('/add_deposit', methods=['POST'])
def add_deposit():
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    saving_name = request.json.get('saving_name')
    descrip = request.json.get('descrip')

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(
            "INSERT INTO deposits (saving_name, descrip) VALUES (%s, %s)",
            (saving_name, descrip)
        )
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '存款业务添加成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'添加存款业务失败: {str(err)}'})


# 删除存款业务
@app.route('/delete_deposit/<int:saving_id>', methods=['POST'])
def delete_deposit(saving_id):
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM deposits WHERE saving_id = %s", (saving_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '存款业务删除成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'删除存款业务失败: {str(err)}'})


# 交易管理页面
@app.route('/trades')
def trades():
    if 'user_id' not in session or session['username'] != 'admin':
        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM trades")
    trades = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('trades.html', trades=trades)


# 添加交易
@app.route('/add_trade', methods=['POST'])
def add_trade():
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    trade_type = request.json.get('trade_type')
    trade_money = request.json.get('trade_money')
    card_id = request.json.get('card_id')
    remark = request.json.get('remark')

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(
            "INSERT INTO trades (trade_type, trade_money, card_id, remark) VALUES (%s, %s, %s, %s)",
            (trade_type, trade_money, card_id, remark)
        )
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '交易添加成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'添加交易失败: {str(err)}'})


# 删除交易
@app.route('/delete_trade/<int:trade_id>', methods=['POST'])
def delete_trade(trade_id):
    if 'user_id' not in session or session['username'] != 'admin':
        return jsonify({'success': False, 'message': '权限不足'})

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM trades WHERE trade_id = %s", (trade_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'success': True, 'message': '交易删除成功'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'删除交易失败: {str(err)}'})


# 客户页面
@app.route('/customer')
def customer():
    if 'user_id' not in session or session['username'] == 'admin':
        return redirect(url_for('index'))
    return render_template('customer.html')


# balance.html
@app.route('/balance')
def balance():
    if 'user_id' not in session:
        return redirect(url_for('index'))

    user_id = session['user_id']

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT balance FROM cards WHERE user_id = %s", (user_id,))
    balance_data = cursor.fetchone()
    cursor.close()
    conn.close()

    balance = balance_data[0] if balance_data else 0

    return render_template('balance.html', balance=balance)


# 存款页面
@app.route('/deposit')
def deposit_page():
    if 'user_id' not in session:
        return redirect(url_for('index'))
    return render_template('deposit.html')


# 存款操作
@app.route('/deposit', methods=['POST'])
def deposit():
    if 'user_id' not in session:
        return jsonify({'success': False, 'message': '权限不足'})

    amount = request.json.get('amount')
    username = session['username']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 更新余额
        cursor.execute(
            "UPDATE cards SET balance = balance + %s WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)",
            (amount, username)
        )

        # 插入交易记录
        cursor.execute(
            "INSERT INTO trades (trade_type, trade_money, card_id) VALUES ('存款', %s, (SELECT card_id FROM cards WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)))",
            (amount, username)
        )

        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({'success': True, 'message': '存款成功', 'redirect_url': url_for('customer')})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'存款失败: {str(err)}'})


# 取款页面
@app.route('/withdraw')
def withdraw_page():
    if 'user_id' not in session:
        return redirect(url_for('index'))
    return render_template('withdraw.html')


# 取款操作
@app.route('/withdraw', methods=['POST'])
def withdraw():
    if 'user_id' not in session:
        return jsonify({'success': False, 'message': '权限不足'})

    amount = request.json.get('amount')
    username = session['username']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 检查余额是否足够
        cursor.execute(
            "SELECT balance FROM cards WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)",
            (username,)
        )
        balance = cursor.fetchone()[0]

        if balance >= amount:
            # 更新余额
            cursor.execute(
                "UPDATE cards SET balance = balance - %s WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)",
                (amount, username)
            )

            # 插入交易记录
            cursor.execute(
                "INSERT INTO trades (trade_type, trade_money, card_id) VALUES ('取款', %s, (SELECT card_id FROM cards WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)))",
                (amount, username)
            )

            conn.commit()
            cursor.close()
            conn.close()

            return jsonify({'success': True, 'message': '取款成功', 'redirect_url': url_for('customer')})
        else:
            cursor.close()
            conn.close()
            return jsonify({'success': False, 'message': '余额不足'})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'取款失败: {str(err)}'})


# 挂失页面
@app.route('/report_loss')
def report_loss_page():
    if 'user_id' not in session:
        return redirect(url_for('index'))
    return render_template('report_loss.html')


# 挂失操作
@app.route('/report_loss', methods=['POST'])
def report_loss():
    if 'user_id' not in session:
        return jsonify({'success': False, 'message': '权限不足'})

    username = session['username']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 更新银行卡状态
        cursor.execute(
            "UPDATE cards SET is_report_loss = TRUE WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s)",
            (username,)
        )

        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({'success': True, 'message': '挂失成功', 'redirect_url': url_for('customer')})
    except mysql.connector.Error as err:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': f'挂失失败: {str(err)}'})


# 修改密码页面
@app.route('/change_password')
def change_password_page():
    if 'user_id' not in session:
        return redirect(url_for('index'))
    return render_template('change_password.html')


# 修改密码操作
@app.route('/change_password', methods=['POST'])
def change_password():
    if 'user_id' not in session:
        return jsonify({'success': False, 'message': '权限不足'})

    old_password = request.json.get('old_password')
    new_password = request.json.get('new_password')
    username = session['username']

    conn = get_db_connection()
    cursor = conn.cursor()

    # 验证旧密码
    cursor.execute(
        "SELECT * FROM users WHERE username = %s AND password = %s",
        (username, old_password)
    )
    user = cursor.fetchone()

    if user:
        # 更新密码
        cursor.execute(
            "UPDATE users SET password = %s WHERE username = %s",
            (new_password, username)
        )
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({'success': True, 'message': '密码修改成功', 'redirect_url': url_for('logout')})
    else:
        cursor.close()
        conn.close()
        return jsonify({'success': False, 'message': '旧密码错误'})


# 导出报表页面
@app.route('/export_report')
def export_report_page():
    if 'user_id' not in session:
        return redirect(url_for('index'))
    return render_template('export_report.html')


# 导出报表操作
@app.route('/export_report')
def export_report():
    if 'user_id' not in session:
        return jsonify({'success': False, 'message': '权限不足'})

    username = session['username']

    conn = get_db_connection()
    cursor = conn.cursor()

    # 查询交易记录
    cursor.execute(
        "SELECT * FROM trades WHERE card_id = (SELECT card_id FROM cards WHERE customer_id = (SELECT customer_id FROM customers WHERE username = %s))",
        (username,)
    )
    trades = cursor.fetchall()
    cursor.close()
    conn.close()

    # 生成 CSV 文件
    output = StringIO()
    writer = csv.writer(output)
    writer.writerow(['交易ID', '交易时间', '交易类型', '交易金额', '银行卡号', '备注'])
    for trade in trades:
        writer.writerow(trade)

    return output.getvalue(), 200, {
        'Content-Type': 'text/csv',
        'Content-Disposition': 'attachment; filename=trade_report.csv'
    }


# 注销
@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('username', None)
    return redirect(url_for('index'))




if __name__ == '__main__':
    app.run(debug=True)